﻿/*
 * 编写者 ：张维锋
 * 时   间：2011-5-25
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

#region 数据操作类
namespace Orf.dto
{
    public class SqlHelper
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;
        private SqlDataAdapter da = null;

        #region 创建conn
        /// <summary>
        ///生成到配置文件的连接数据库字符串
        /// </summary>
        public SqlHelper()
        {
            string strConntionName = "strCon";
            ConnectionStringSettings setting;
            setting = ConfigurationManager.ConnectionStrings[strConntionName];

            if (setting == null)
            {
                setting = new ConnectionStringSettings();
                setting.Name = strConntionName;
                setting.ConnectionString = "server=127.0.0.1;Initial Catalog=MZXT;uid=sa;pwd=sql;Asynchronous Processing=True;";
                Configuration config;
                config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
                config.ConnectionStrings.ConnectionStrings.Add(setting);
                config.Save();
            }
            conn = new SqlConnection(setting.ConnectionString);
        }
        #endregion

        #region 打开数据库连接
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public SqlConnection sqlOpen()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        #endregion

        #region 关闭数据库连接
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void sqlClose()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 执行不带参数的SQL增删改语句或者存储过程
        /// <summary>
        /// 执行不带参数的SQL增删改语句
        /// </summary>
        /// <param name="comText">SQL语句或者存储过程</param>
        /// <param name="ct">命令类型（sql语句或者存储过程）</param>
        /// <returns>返回更新的记录数</returns>
        public int ExecuteNonQuery(string comText, CommandType ct)
        {
            int res;
            try
            {
                using (cmd = getCommand(comText, ct))
                {
                    res = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return res;
        }
        #endregion

        #region 执行带参数的SQL增删改语句或者存储过程
        /// <summary>
        /// 执行带参数的SQL增删改语句
        /// </summary>
        /// <param name="comText">SQL语句或者存储过程</param>
        /// <param name="paras">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <returns>返回更新的记录数</returns>
        public int ExecuteNonQuery(string comText, SqlParameter[] paras, CommandType ct)
        {
            int res;
            try
            {
                using (cmd = getCommand(comText, paras, ct))
                {
                    res = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return res;
        }
        #endregion

        #region 执行不带参数的SQL查询语句或者存储过程
        /// <summary>
        /// 执行不带参数的SQL查询语句或者存储过程
        /// </summary>
        /// <param name="comText">SQL查询语句或者存储过程</param>
        /// <param name="ct"> 命令类型</param>
        /// <returns>返回数据表</returns>
        public DataTable ExecuteQuery(string comText, CommandType ct)
        {
            DataTable dt = new DataTable();
            try
            {
                cmd = getCommand(comText, ct);
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.ToString());
            }
            return dt;
        }
        #endregion

        #region 执行带参数的SQL查询语句或存储过程
        /// <summary>
        /// 执行带参数的SQL查询语句或存储过程
        /// </summary>
        /// <param name="comText">SQL查询语句或存储过程</param>
        /// <param name="paras">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <returns>返回数据表</returns>
        public DataTable ExecuteQuery(string comText, SqlParameter[] paras, CommandType ct)
        {
            DataTable dt = new DataTable();
            try
            {
                cmd = getCommand(comText, paras, ct);
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.ToString());
            }
            return dt;
        }
        #endregion

        #region 不带参数的方法getDataReader
        /// <summary>
        ///方法方法getDataReader
        /// </summary>
        /// <param name="comText">执行sql语句或者存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public SqlDataReader getDataReader(string comText, CommandType ct)
        {
            SqlDataReader dr = null;
            try
            {
                using (cmd = getCommand(comText, ct))
                {
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return dr;
        }
        #endregion

        #region 带参数的方法getDataReader
        /// <summary>
        /// 带参数的方法getDataReader
        /// </summary>
        /// <param name="comText">SQL语句或存储过程</param>
        /// <param name="paras">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public SqlDataReader getDataReader(string comText, SqlParameter[] paras, CommandType ct)
        {
            SqlDataReader dr = null;
            try
            {
                using (cmd = getCommand(comText, paras, ct))
                {
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return dr;
        }
        #endregion

        #region 带参数的getCommand方法
        /// <summary>
        /// 带参数的命令型getCommand方法
        /// </summary>
        /// <param name="comText">SQL语句或存储过程</param>
        /// <param name="paras">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public SqlCommand getCommand(string comText, SqlParameter[] paras, CommandType ct)
        {
            try
            {
                using (cmd = new SqlCommand(comText, sqlOpen()))
                {
                    cmd.CommandType = ct;
                    cmd.Parameters.AddRange(paras);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return cmd;
        }
        #endregion

        #region 带参数的getCommand方法
        /// <summary>
        /// 不带参数的命令型getCommand方法
        /// </summary>
        /// <param name="comText">执行sql语句或者存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public SqlCommand getCommand(string comText, CommandType ct)
        {
            try
            {
                using (cmd = new SqlCommand(comText, sqlOpen()))
                {
                    cmd.CommandType = ct;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return cmd;
        }
        #endregion

        #region 带参数的DataSet方法，有传入表名的
        /// <summary>
        /// 带参数的DataSet方法，有传入表名的
        /// </summary>
        /// <param name="comText">SQL语句或存储过程</param>
        /// <param name="paras">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <param name="tablename">表名称</param>
        /// <returns></returns>
        public DataSet getDataSet(string comText, SqlParameter[] paras, CommandType ct, string tablename)
        {
            DataSet dataSet = new DataSet();
            try
            {
                using (da = new SqlDataAdapter())
                {
                    da.SelectCommand = getCommand(comText, paras, ct);
                    da.Fill(dataSet, tablename);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            return dataSet;
        }
        #endregion

        #region 不带参数的DataSet方法，有传入表名的
        /// <summary>
        ///  不带参数的DataSet方法，有传入表名的
        /// </summary>
        /// <param name="comText">执行sql语句或者存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <param name="tablename">表名称</param>
        /// <returns>dataSet</returns>
        public DataSet getDataSet(string comText, CommandType ct, string tablename)
        {
            DataSet dataSet = new DataSet();
            try
            {
                using (da = new SqlDataAdapter())
                {
                    da.SelectCommand = getCommand(comText, ct);
                    da.Fill(dataSet, tablename);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            return dataSet;
        }
        #endregion


        #region 带参数的DataSet方法，没有传入表名的
        /// <summary>
        /// 带参数的DataSet方法，没有传入表名的
        /// </summary>
        /// <param name="comText">SQL语句或存储过程</param>
        /// <param name="paras">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <param name="tablename">表名称</param>
        /// <returns></returns>
        public DataSet getDataSet(string comText, SqlParameter[] paras, CommandType ct)
        {
            DataSet dataSet = new DataSet();
            try
            {
                using (da = new SqlDataAdapter())
                {
                    da.SelectCommand = getCommand(comText, paras, ct);
                    da.Fill(dataSet);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            return dataSet;
        }
        #endregion

        #region 不带参数的DataSet方法，没有传入表名的
        /// <summary>
        ///  不带参数的DataSet方法，没有传入表名的
        /// </summary>
        /// <param name="comText">执行sql语句或者存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <param name="tablename">表名称</param>
        /// <returns>dataSet</returns>
        public DataSet getDataSet(string comText, CommandType ct)
        {
            DataSet dataSet = new DataSet();
            try
            {
                using (da = new SqlDataAdapter())
                {
                    da.SelectCommand = getCommand(comText, ct);
                    da.Fill(dataSet);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }

            return dataSet;
        }
        #endregion

        #region
        /// <summary>
        /// 获取某个字段数据
        /// </summary>
        /// <param name="sql">查询的SQL语句</param>
        /// <returns></returns>
        public string Findstring(string sql, CommandType ct)
        {
            string hang = null;
            try
            {
                using (cmd = getCommand(sql, ct))
                {
                    hang = Convert.ToString(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return hang;
        }
        #endregion

        #region 对GridView进行数据绑定，无排序
        /// <summary>
        /// 对GridView进行数据绑定，无排序
        /// </summary>
        /// <param name="sql">sql查询语句或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <param name="dg"></param>
        public void bindGridView(string sql, CommandType ct, DataGridView dg)
        {
            DataSet ds = null;
            try
            {
                ds = getDataSet(sql, ct);
                dg.DataSource = ds.Tables[0].DefaultView;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        #endregion

        #region
        /// <summary>
        /// 对ComBox进行数据绑定
        /// </summary>
        /// <param name="sql">sql查询语句或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <param name="cbs"></param>
        /// <param name="member">绑定的字段</param>
        public void bindCombox(string sql, CommandType ct, ComboBox cbs,string member)
        {
            DataSet ds = null;
            try
            {
                ds = getDataSet(sql, ct);
                cbs.DataSource = ds.Tables[0];
                cbs.DisplayMember = member;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        #endregion

        #region 执行事务
        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sql">sql语句或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <param name="back_message">传入的参数</param>
        public void ExectCommand(string sql,CommandType ct, string back_message)
        {
            SqlConnection conn = sqlOpen();
            SqlTransaction tran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sqlOpen();
            cmd.Transaction =tran;
            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = ct;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show(back_message);
            }
            catch (Exception ex)
            {
                tran.Rollback();
                MessageBox.Show("执行失败，原因：" + ex.ToString());
            }
            finally 
            {
                conn.Close();
            }
        }
        #endregion

        #region 多事务执行
        /// <summary>
        /// 多事务执行
        /// </summary>
        /// <param name="sql">sql语句或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <param name="back_message">传入的参数</param>
        public void ExectCommand(string sql0,string sql1, string back_message)
        {
            SqlConnection conn = sqlOpen();
            SqlTransaction tran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = sqlOpen();
            cmd.Transaction =tran;
            try
            {
                cmd.CommandText = sql0;
                cmd.ExecuteNonQuery();
                cmd.CommandText = sql1;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show(back_message);
            }
            catch (Exception ex)
            {
                tran.Rollback();
                MessageBox.Show("执行失败，原因：" + ex.ToString());
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
    }
}
#endregion